package persistence.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Vector;

import search.CompassSearchEngine;
import server.Statistics;
import domain.items.ForumThread;
import domain.items.Item;
import domain.items.Post;
import domain.items.Topic;
import interfaces.DataRepository;

public class JDBCDataRepository implements DataRepository {

	/**
	 * root Topic, the most upper node of the forum
	 */
	private Topic _root = null;

	/** sqlServer parameters */
	private Connection _sqlConnection;
	private String _driverName = "com.mysql.jdbc.Driver";
	private SimpleDateFormat _sqlDateFormat = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss");
	private Date _dayZero;
	
	

	public JDBCDataRepository() {
		try {
			_dayZero = this._sqlDateFormat.parse("2009-08-21 07:00:00");
		} catch (ParseException e1) {
			System.out.println(e1.getMessage());
		}
		JDBC_Config.SetConfig();
		try {
			Class.forName(_driverName).newInstance();

		} catch (InstantiationException e) {
			System.out.println(e.getMessage());
		} catch (IllegalAccessException e) {
			System.out.println(e.getMessage());
		} catch (ClassNotFoundException e) {
			System.out.println(e.getMessage());
		}
		try {
			JDBC_Config.SetConfig();
			_sqlConnection = DriverManager.getConnection(JDBC_Config.URL
					+ JDBC_Config.DBNAME, JDBC_Config.USERNAME,
					JDBC_Config.PASSWORD);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		buildRoot();

	}

	public synchronized Topic getRootTopic() {
		return this._root;
	}

	private void buildRoot() {
		this._root = new Topic(null);
		this._root.setFather(this._root);
		this._root.setId(1);
		this._root.setName("Root");
		try {
			Statement s = this._sqlConnection.createStatement();
			String sql = "SELECT * FROM items ORDER BY id ASC";
			ResultSet r = s.executeQuery(sql);
			r.next();
			while (r.next()) { // has more lines in table Items
				int id = r.getInt("id");
				int type = r.getInt("type");
				String name = r.getString("name");
				Item newObjectFromTable = null;
				Item father = getFatherObject(id / 100);
				if (type == Item.TOPIC) { // topic
					newObjectFromTable = new Topic(father);
					newObjectFromTable.setName(name);
					newObjectFromTable.setId(id);
					((Topic) father).getSubItems().add(newObjectFromTable);
				} else if (type == Item.FORUM_THREAD) { // thread
					newObjectFromTable = new ForumThread(father);
					newObjectFromTable.setName(name);
					newObjectFromTable.setId(id);
					((Topic) father).getSubItems().add(newObjectFromTable);
				} else if (type == Item.POST) { // post
					s = this._sqlConnection.createStatement();
					sql = "SELECT * FROM items JOIN posts WHERE posts.id = "
							+ id;
					ResultSet r2 = s.executeQuery(sql);
					if (r2.next()) {
						String owner = r2.getString("owner");
						String content = r2.getString("content");
						Timestamp date = r2.getTimestamp("date");
						newObjectFromTable = new Post(father, owner, name,
								content);
						newObjectFromTable.setId(id);
						((Post) newObjectFromTable).setDate(date);
						((ForumThread) father).getPosts().add(
								(Post) newObjectFromTable);
						CompassSearchEngine.getInstance().updateSearchDB((Post)newObjectFromTable, null);
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e.getMessage());
		}
	}

	public boolean addPost(Post post) {
		ForumThread tthread = (ForumThread) post.getFather();

		try {
			post.setId(tthread.getId() * 100 + tthread.getPosts().size());
			tthread.getPosts().add(post);

			Statement s = this._sqlConnection.createStatement();
			String sql = "INSERT INTO items VALUES (" + post.getId() + ",'"
					+ post.getName() + "',2);";
			s.executeUpdate(sql);
			sql = "INSERT INTO posts VALUES (" + post.getId() + ",'"
					+ post.getText() + "','" + post.getOwner() + "','"
					+ this._sqlDateFormat.format(post.getDate()) + "');";
			s.executeUpdate(sql);
			sql = "INSERT INTO threads VALUES (" + tthread.getId() + ","
					+ post.getId() + ");";
			s.executeUpdate(sql);
			updateNewPostStat(post);
			return true;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
	}

	public boolean addThread(ForumThread newThread) {
		Topic ttopic = (Topic) newThread.getFather();
		try {
			newThread.setId(ttopic.getId() * 100 + ttopic.getSubItems().size());
			ttopic.getSubItems().add(newThread);
			Statement s = this._sqlConnection.createStatement();
			String sql = "INSERT INTO items VALUES (" + newThread.getId()
					+ ",'" + newThread.getName() + "',1);";
			s.executeUpdate(sql);
			sql = "INSERT INTO topics VALUES (" + ttopic.getId() + ","
					+ newThread.getId() + ");";
			s.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
	}

	public boolean addTopic(Topic newTopic) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean deletePost(Post post) {

		try {
			ForumThread tFatherThread = (ForumThread) post.getFather();

			// step 1: delete post Object from the tree
			tFatherThread.getPosts().removeElement(post);

			// step 2: delete post from database (deleting from 'items' will
			// cascade to other tables)
			Statement s = this._sqlConnection.createStatement();
			String sql = "DELETE FROM items WHERE id=" + post.getId() + ";";
			s.executeUpdate(sql);

			int fatherThreadSize = tFatherThread.getPosts().size();

			// step 3: update all succeeding posts id's
			int postIndex = calculatePostIndex(post.getId());
			for (int i = postIndex; i < fatherThreadSize; i++) {
				// change object id
				Post currentPost = tFatherThread.getPosts().elementAt(i);
				int oldID = (int) currentPost.getId();
				decrementPostID(currentPost);
				// change item id at database
				sql = "UPDATE items SET id=" + (oldID - 1) + " WHERE id="
						+ oldID + ";";
				s.executeUpdate(sql);
			}

			// step 4: if this is the only post in the thread, delete the thread
			if (fatherThreadSize == 0) {
				deleteThread(tFatherThread);
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		return true;
	}

	public boolean deleteThread(ForumThread thread) {
		try {
			Topic tFatherTopic = (Topic) thread.getFather();

			// step 1: delete thread Object from the tree
			tFatherTopic.getSubItems().removeElement(thread);

			// step 2: delete thread from database (deleting from 'items' will
			// cascade to other tables)
			Statement s = this._sqlConnection.createStatement();
			String sql = "DELETE FROM items WHERE id=" + thread.getId() + ";";
			s.executeUpdate(sql);

			int fatherTopicSize = tFatherTopic.getSubItems().size();

			// step 3: update all succeeding threads id's
			int threadIndex = calculateThreadIndex(thread.getId());
			for (int i = threadIndex; i < fatherTopicSize; i++) {
				System.out.println("here 1 i=" + i);
				// change object id
				ForumThread currentThread = (ForumThread) tFatherTopic
						.getSubItems().elementAt(i);
				int oldID = (int) currentThread.getId();
				decrementThreadID(currentThread);
				// change item id at database
				sql = "UPDATE items SET id=" + (oldID - 1) + " WHERE id="
						+ oldID + ";";
				s.executeUpdate(sql);
				// cascade change to all posts of current thread
				int currentThreadSize = currentThread.getPosts().size();
				for (int j = 0; j < currentThreadSize; j++) {
					Post currentPost = currentThread.getPosts().elementAt(j);
					long oldPostId = currentPost.getId();
					long newPostId = oldPostId - 100;
					currentPost.setId(newPostId);
					sql = "UPDATE items SET id=" + (newPostId) + " WHERE id="
							+ oldPostId + ";";
					s.executeUpdate(sql);
				}
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		return true;

	}

	private void decrementThreadID(ForumThread thread) {
		thread.setId(thread.getId() - 1);

	}

	private int calculateThreadIndex(long id) {
		System.out.println("inside calculate thread index result is "
				+ (int) id % 10000);
		return (int) id % 100;
	}

	private int calculatePostIndex(long id) {
		// example: calculatePostIndex(10204) should return 4
		return (int) id % 100;
	}

	private void decrementPostID(Post post) {
		// decrementPostID(10204) --> 10203
		post.setId(post.getId() - 1);

	}

	public boolean updatePost(Post post) {
		String newSubject = post.getName();
		String newContent = post.getText();
		int id = (int) post.getId();
		try {
			Statement s = this._sqlConnection.createStatement();
			String sql = "UPDATE items SET name='" + newSubject + "' WHERE id="
					+ id + ";";
			s.executeUpdate(sql);
			sql = "UPDATE posts SET content='" + newContent + "' WHERE id="
					+ id + ";";
			s.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

	}

	private Item getFatherObject(int id) {
		Item father = this._root;
		if (id == 1)
			return father;
		else {
			String index = "" + id;
			for (int k = 1; k < index.length(); k += 2) {
				father = ((Topic) father).getSubItems().elementAt(
						Integer.parseInt("" + index.charAt(k)
								+ index.charAt(k + 1)));
			}
			return father;
		}
	}
	
	public void generateStat(int type, String name) {
		if (type == 1) {
			generateStat1(name);
		}
		if (type == 2) {
			generateStat2();
		}
		if (type == 3) {
			generateStat3();
		}
	}
	
	private void generateStat1(String name) {
		String startDate = getOneMonthAgo();
		Vector<Integer> numOfPosts = new Vector<Integer>();
		for (int i=0; i<31; i++) {
			numOfPosts.add(0);
		}
		try {
			Statement s = this._sqlConnection.createStatement();
			String sql = "SELECT * FROM posts WHERE owner='"+name+"' AND date>'"+startDate+"' ORDER BY date ASC";
			ResultSet r = s.executeQuery(sql);
			r.next();
			while (r.next()) {
				Date postDate = r.getTimestamp("date");
				int daysBeforeNow = this.getDaysPassed(postDate, new Date());
				int oldValue = numOfPosts.elementAt(daysBeforeNow);
				numOfPosts.set(daysBeforeNow, oldValue+1);
			}
			Statistics.generateStat1(numOfPosts, name);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	public void generateStat2() {
		int daysPassed = this.getDaysPassed(_dayZero, new Date());
		Vector<Integer> values = new Vector<Integer>();
		try {
			Statement s = this._sqlConnection.createStatement();
			String sql = "SELECT * FROM stat2 ORDER BY hour ASC";
			ResultSet r = s.executeQuery(sql);
			r.next();
			while (r.next()) {
				int value = r.getInt("value");
				values.add(value);
			}
			Statistics.generateStat2(values, daysPassed);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	private void generateStat3() {
		int daysPassed = this.getDaysPassed(_dayZero, new Date());
		Vector<Integer> values = new Vector<Integer>();
		try {
			Statement s = this._sqlConnection.createStatement();
			String sql = "SELECT * FROM stat3 ORDER BY hour ASC";
			ResultSet r = s.executeQuery(sql);
			r.next();
			while (r.next()) {
				int value = r.getInt("value");
				values.add(value);
			}
			Statistics.generateStat3(values, daysPassed);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	private String getOneMonthAgo() {
		Calendar c = new GregorianCalendar();
	    c.add(Calendar.DATE, -30);
		return _sqlDateFormat.format(c.getTime());
	}
	
	public int getDaysPassed(Date from, Date to) {
		Calendar c1 = new GregorianCalendar();
		Calendar c2 = new GregorianCalendar();
		c1.setTime(to);
		c2.setTime(from);
		long diff = c1.getTimeInMillis() - c2.getTimeInMillis();
		long res = diff/1000/60/60/24;
		return (int)res;
	}

	// collect data for statistic #2
	public void updateNewPostStat(Post post) {
		Statement s;
		try {
			s = this._sqlConnection.createStatement();
			Calendar c = new GregorianCalendar();
			c.setTime(post.getDate());
			int h = c.get(Calendar.HOUR_OF_DAY);
			String sql = "UPDATE stat2 SET value = value + 1 WHERE hour = "+h+";";
			s.executeUpdate(sql);
		}	catch (SQLException e) {
				System.out.println(e.getMessage());
		}

	}
	
	//collect data for statistic #3
	public void updateUserLoginStat() {
		try {
			Statement s = this._sqlConnection.createStatement();
			Calendar c = new GregorianCalendar();
			c.setTime(new Date());
			int h = c.get(Calendar.HOUR_OF_DAY);
			String sql = "UPDATE stat3 SET value = value + 1 WHERE hour = "+h+";";
			s.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
}
